package com.tistory.commons.utils;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tistory.credit.vo.CreditReq;
import com.tistory.login.vo.MemberSearchInfo;


public class XlsxReadRealBank {
	private static final Logger log = LoggerFactory.getLogger(XlsxReadRealBank.class);
	public static List<CreditReq> parsingExcel(String filepath,String userId,String reqDate,MemberSearchInfo searchInfo,String payProdFee){
		XSSFRow row;
		XSSFCell cell;
		List<CreditReq>creditList = new ArrayList<CreditReq>();
		boolean checkListflag = false;
		boolean compareProdFree = false;
		try {

			FileInputStream inputStream = new FileInputStream(filepath);
			XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

		
			//sheet수 취득
			int sheetCn = workbook.getNumberOfSheets();
			log.info("sheet수 : " + sheetCn);

			for(int cn = 0; cn < sheetCn; cn++){
				log.info("취득하는 sheet 이름 : " + workbook.getSheetName(cn));
				log.info(workbook.getSheetName(cn) + " sheet 데이터 취득 시작");

				//0번째 sheet 정보 취득
				XSSFSheet sheet = workbook.getSheetAt(cn);

				//취득된 sheet에서 rows수 취득
				int rows = sheet.getPhysicalNumberOfRows();
				log.info(workbook.getSheetName(cn) + " sheet의 row수 : " + rows);

				//취득된 row에서 취득대상 cell수 취득
				int cells = sheet.getRow(cn).getPhysicalNumberOfCells(); //
				log.info(workbook.getSheetName(cn) + " sheet의 row에 취득대상 cell수 : " + cells);

				for (int r = rows; r >=1; r--) {
					CreditReq req = new CreditReq();
					row = sheet.getRow(r); // row 가져오기
					if (row != null) {
						for (int c = 0; c < cells; c++) {
							cell = row.getCell(c);
							if (cell != null) {
								String value = null;
								switch (cell.getCellType()) {
								case XSSFCell.CELL_TYPE_FORMULA:
									value = cell.getCellFormula();
									break;
								case XSSFCell.CELL_TYPE_NUMERIC:
//									value = "" + cell.getNumericCellValue();
									value = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
									break;
								case XSSFCell.CELL_TYPE_STRING:
									value = "" + cell.getStringCellValue();
									break;
								case XSSFCell.CELL_TYPE_BLANK:
									value = "[null 아닌 공백]";
									break;
								case XSSFCell.CELL_TYPE_ERROR:
									value = "" + cell.getErrorCellValue();
									break;
								default:
								}

								switch (c) {
								case 1:
									req.setUserId(userId);
									req.setTransDate(value);
									req.setFlagCd("20");
									break;
								case 3:
									if(		  searchInfo.getBcNumberFirst().equals(value)
											|| searchInfo.getBcNumberSecond().equals(value)		
											|| searchInfo.getExceptBcNumber().equals(value)
											){
										checkListflag = true;
									}
									break;
								case 4:
									req.setOriProdAmt(value);
									if(checkListflag){	// 자기것만 저장할수 있는 flag
										if(!compareProdFree){
											value = value.replace(",", "");
											payProdFee = payProdFee.replace(",", "");
											if(Long.parseLong(value) >= Long.parseLong(payProdFee)){
												Long minusFee = Long.parseLong(value)-Long.parseLong(payProdFee);
												req.setProdAmt(minusFee.toString());
												compareProdFree = true;
											}else{
												req.setProdAmt(value);	
											}
										}else{
											req.setProdAmt(value);
										}
									}								
									break;									
								case 7:
									if(value.indexOf("영업부")>-1){
										req.setCardComCd("10");
										req.setCardDetail("비씨카드회수");
										req.setCardCom("비씨카드");
									}else if (value.indexOf("국민")>-1){
										req.setCardComCd("11");
										req.setCardDetail("국민카드회수");
										req.setCardCom("국민카드");
									}else if (value.indexOf("외환카드")>-1){
										req.setCardComCd("12");
										req.setCardCom("외환카드");
									}else if (value.indexOf("SC")>-1){
										req.setCardComCd("13");
										req.setCardDetail("삼성카드회수");
										req.setCardCom("삼성카드");
									}else if (value.indexOf("여의도")>-1){
										req.setCardComCd("15");
										req.setCardDetail("현대카드회수");
										req.setCardCom("현대카드");
									}else if (value.indexOf("강남")>-1){
										req.setCardComCd("16");
										req.setCardDetail("롯데카드회수");
										req.setCardCom("롯데카드");
									}else if (value.indexOf("신한")>-1){
										req.setCardComCd("17");
										req.setCardDetail("신한카드회수");
										req.setCardCom("신한카드");
									}else if (value.indexOf("전북")>-1){
										req.setCardComCd("18");
									}else if (value.indexOf("외환")>-1){
										req.setCardComCd("19");
										req.setCardDetail("하나카드회수");
										req.setCardCom("하나카드");
									}else if (value.indexOf("농협")>-1){
										req.setCardComCd("20");
										req.setCardDetail("농협카드회수");
										req.setCardCom("농협카드");
									}else{
										req.setCardComCd("21");
									}	

								default:
									break;
								}								
							} 

						} // for(c) 문
					}
					if(checkListflag){	// 자기것만 저장할수 있는 flag
						creditList.add(req);
						checkListflag = false;
					}
				} // for(r) 문
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return creditList;
	}

}